package org.nf.dao.impl;

import org.nf.dao.StaffsDao;
import org.nf.entity.Staffs;
import org.nf.util.DbUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zhu
 * @date 2024-03-06
 */
public class StaffsDaoImpl implements StaffsDao {
    @Override
    public int insert(Staffs staffs) {
        int row = 0;
        String sql = "insert into staffs values(null,?,?,?,?,?,?);";

        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;

        try {
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1,staffs.getName());
            pstmt.setInt(2,staffs.getAge());
            pstmt.setString(3,staffs.getPhone());
            pstmt.setString(4,staffs.getPos());
            pstmt.setTimestamp(5, Timestamp.valueOf(staffs.getAddTime()));
            pstmt.setTimestamp(6,Timestamp.valueOf(staffs.getUpdateTime()));
            row = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(null,pstmt,conn);
        }
        return row;
    }

    @Override
    public int delete(int id) {
        int row = 0;
        String sql = "delete from staffs where id=?;";

        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;

        try {
            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1,id);
            row = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(null,pstmt,conn);
        }
        return row;
    }

    @Override
    public int update(Staffs staffs) {
        int row = 0;
        String sql = "update staffs set name=?,age=?,phone=?,pos=?,add_time=?,update_time=? where id=?;";

        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;

        try {
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1,staffs.getName());
            pstmt.setInt(2,staffs.getAge());
            pstmt.setString(3,staffs.getPhone());
            pstmt.setString(4,staffs.getPos());
            pstmt.setTimestamp(5, Timestamp.valueOf(staffs.getAddTime()));
            pstmt.setTimestamp(6,Timestamp.valueOf(staffs.getUpdateTime()));
            pstmt.setInt(7,staffs.getId());
            row = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(null,pstmt,conn);
        }
        return row;
    }

    @Override
    public List<Staffs> select() {
        List<Staffs> staffsList =new ArrayList<>();

        String sql = "select * from staffs";

        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rst = null;

        try {
            pstmt = conn.prepareStatement(sql);

            rst = pstmt.executeQuery();

            while (rst.next()){
                Staffs staffs = new Staffs();
                staffs.setId(rst.getInt(1));
                staffs.setName(rst.getString(2));
                staffs.setAge(rst.getInt(3));
                staffs.setPhone(rst.getString(4));
                staffs.setPos(rst.getString(5));
                staffs.setAddTime(rst.getTimestamp(6).toLocalDateTime());
                staffs.setUpdateTime(rst.getTimestamp(7).toLocalDateTime());
                staffsList.add(staffs);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(rst,pstmt,conn);
        }
        return staffsList;
    }

    @Override
    public Staffs selectById(int id) {
        Staffs staffs = null;
        String sql = "select * from staffs where id=?";

        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rst = null;

        try {
            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1,id);
            rst = pstmt.executeQuery();

            while (rst.next()){
                staffs = new Staffs();
                staffs.setId(rst.getInt(1));
                staffs.setName(rst.getString(2));
                staffs.setAge(rst.getInt(3));
                staffs.setPhone(rst.getString(4));
                staffs.setPos(rst.getString(5));
                staffs.setAddTime(rst.getTimestamp(6).toLocalDateTime());
                staffs.setUpdateTime(rst.getTimestamp(7).toLocalDateTime());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(rst,pstmt,conn);
        }
        return staffs;
    }

    @Override
    public List<Staffs> selectByPage(Staffs staffs, Integer page, Integer limit) {
        List<Staffs> list = new ArrayList<>();

        StringBuilder sql = new StringBuilder("select id,name,age,phone,pos,add_time,update_time from ( ");
        sql.append(" SELECT id,name,age,phone,pos,add_time,update_time, ROW_NUMBER() OVER() AS rowNum ");
        sql.append(" from staffs ) as t ");

        // 1.定义集合，用于存储 SQL 语句的 ？占位符的参数值
        List<Object> params = new ArrayList<Object>();

        sql.append(" where rowNum between  ? and ?");

        // 添加分页参数
        params.add((page - 1) * limit + 1);
        params.add(page * limit);

        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rst = null;

        try {
            pstmt = conn.prepareStatement(sql.toString());

            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i+1,params.get(i));
            }

            rst = pstmt.executeQuery();

            while (rst.next()){
                Staffs s = new Staffs();
                s.setId(rst.getInt(1));
                s.setName(rst.getString(2));
                s.setAge(rst.getInt(3));
                s.setPhone(rst.getString(4));
                s.setPos(rst.getString(5));
                s.setAddTime(rst.getTimestamp(6).toLocalDateTime());
                s.setUpdateTime(rst.getTimestamp(7).toLocalDateTime());
                list.add(s);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(rst,pstmt,conn);
        }
        return list;
    }

    @Override
    public Long selectByPageCount(Staffs staffs) {
        Long count = 0L ;

        // 第一：定义要操作数据库的SQL语句 - 动态多条件查询
        StringBuilder sql = new StringBuilder("select count(id) from staffs ");

        // 第二：获取连接对象
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rst = null;

        try {
            // 第三：预编译SQL语句
            pstmt = conn.prepareStatement(sql.toString());

            // 第五：执行SQL语句
            rst = pstmt.executeQuery();

            // 第六：对执行的结果进行处理
            if (rst.next()) {
                count = rst.getLong(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 第七：关闭相关的对象
            DbUtil.close(rst,pstmt,conn);
        }
        return count;
    }
}
